﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Grishko.SerilizeTools;
using System.Collections;

namespace Grishko.DataTier.SqlSentence
{
    [IsNotSrchStorageLinksObject]  
    public class WhereSentenceWord:TreeEntitySingle 
    {
        /// <summary>
        /// Типы операндов  поля MORE, LESS, EQUALLY,BETWEEN
        /// </summary>
        public enum SentenceWordOperatorTypes { MORE, LESS, NOT_EQUALLY, EQUALLY, LIKE, BETWEEN }

        /// <summary>
        /// Тип слова AND, OR
        /// </summary>
        public enum WhereSentenceWordTypes { AND, OR };


        /// <summary>
        /// Машинка по построению скрипта  поля 
        /// </summary>
        protected class WordFieldScriptor
        {           

            Func<string[], string, string> GetFormatList = delegate(string[] Vars, string FormatString)
            {                
                StringBuilder sb = new StringBuilder();
                for (int i=0;i<Vars.Length;i++)                     
                {
                    if (i>0)
                            sb.Append(" OR ");
                    string[] Params = {Vars[i]};
                    string s = string.Format(FormatString, Params);
                    sb.Append(s);
                }
                return sb.ToString ();
            };

            /// <summary>
            /// Набор разрешенных операций для заданного типа
            /// </summary>
            /// <param name="SentenceWordOperatorTypesVal"></param>
            /// <returns></returns>
            public  static SentenceWordOperatorTypes[] AllowedOperators(SrchItemInfo.PropertyControlType SentenceWordOperatorTypesVal)
            {
            switch (SentenceWordOperatorTypesVal)
            {
                case SrchItemInfo.PropertyControlType.OnlyValueSrch :
                    return new SentenceWordOperatorTypes[] {  SentenceWordOperatorTypes.EQUALLY };

                case SrchItemInfo.PropertyControlType.Text:
                    return new SentenceWordOperatorTypes[] { SentenceWordOperatorTypes.LIKE, SentenceWordOperatorTypes.NOT_EQUALLY, SentenceWordOperatorTypes.EQUALLY, SentenceWordOperatorTypes.LESS, SentenceWordOperatorTypes.MORE };

                case SrchItemInfo.PropertyControlType.Number :
                    return new SentenceWordOperatorTypes[] { SentenceWordOperatorTypes.NOT_EQUALLY, SentenceWordOperatorTypes.EQUALLY, SentenceWordOperatorTypes.LESS, SentenceWordOperatorTypes.MORE, SentenceWordOperatorTypes.BETWEEN };

                case SrchItemInfo.PropertyControlType.Bool:
                    return new SentenceWordOperatorTypes[] {  SentenceWordOperatorTypes.EQUALLY };
                
                case SrchItemInfo.PropertyControlType.Date:
                    return new SentenceWordOperatorTypes[] { SentenceWordOperatorTypes.NOT_EQUALLY, SentenceWordOperatorTypes.EQUALLY, SentenceWordOperatorTypes.BETWEEN };

                case SrchItemInfo.PropertyControlType.Tree:
                    return new SentenceWordOperatorTypes[] { SentenceWordOperatorTypes.LIKE, SentenceWordOperatorTypes.EQUALLY };
                
                //case SrchItemInfo.PropertyControlType.TreeRB:
                //    return new SentenceWordOperatorTypes[] { SentenceWordOperatorTypes.LIKE, SentenceWordOperatorTypes.EQUALLY };                

            }
            return new SentenceWordOperatorTypes[] { };
        }
          
            /// <summary>
            /// Дать sql - заданного поля
            /// </summary>
            /// <param name="FieldValue"></param>
            /// <returns></returns>
            public string GetSql(SentenceWordField FieldValue)
            {

                if (FieldValue.PropertyType == SrchItemInfo.PropertyControlType.OnlyValueSrch)
                    return " TxtPropertyValue LIKE '%" + FieldValue.Value.ToString() + "%'";                 

                if(FieldValue.PropertyType ==SrchItemInfo.PropertyControlType.Text ) 
                        return  GetSQL_TEXT(FieldValue.Value , FieldValue.ID, FieldValue.OperationType);

                if (FieldValue.PropertyType == SrchItemInfo.PropertyControlType.Number)
                    return GetSQL_NUMBER(FieldValue.Value, FieldValue.ID, FieldValue.OperationType);                


                if (FieldValue.PropertyType == SrchItemInfo.PropertyControlType.Date )
                    return GetSQL_Date(FieldValue.Value.ToString(), FieldValue.ID, FieldValue.OperationType);


                if (FieldValue.Value is WordFieldValue_Bool)
                    return GetSQl_Bool((FieldValue.Value as WordFieldValue_Bool).bValue, FieldValue.ID, FieldValue.OperationType);


                if (FieldValue.PropertyType == SrchItemInfo.PropertyControlType.Tree )
                    return GetSQL_Tree(FieldValue.Value, FieldValue.ID, FieldValue.OperationType);

                
                return string.Empty;


            }

            private string GetSQL_Tree(WordFieldValue Value, string Field, SentenceWordOperatorTypes Operation)
            {

                switch (Operation)
                {
                    case SentenceWordOperatorTypes.EQUALLY:
                    case SentenceWordOperatorTypes.LIKE :
                        return " PropertyName = '" + Field + "' AND  PropertyValue = 'OK'";
                }

                return string.Empty; 

            }
            /// <summary>
            /// Формирует sql выражение для поля TXT операциям (=,!=,>,<)
            /// </summary>
            private string GetSQL_TEXT(WordFieldValue  Value,string Field, SentenceWordOperatorTypes Operation )
            {
                switch (Operation)
                {
                    case SentenceWordOperatorTypes.EQUALLY  :
                        return " PropertyName = '" + Field + "' AND  TxtPropertyValue = '" + Value.ToString() + "'"; 

                    case SentenceWordOperatorTypes.NOT_EQUALLY :
                        return " PropertyName = '" + Field + "' AND  NOT (TxtPropertyValue = '" + Value.ToString()  + "') ";  

                    case SentenceWordOperatorTypes.LIKE:
                        {
                            string[] Vars = Value.ToString().Split(",".ToCharArray());
                            string s = GetFormatList(Vars, " TxtPropertyValue  LIKE '%{0}%'");
                            return " PropertyName = '" + Field + "' AND  ("+s +")";
                        }
                }
                
                return string.Empty; 
            }

            /// <summary>
            /// Формирует sql выражение для поля TXT операциям (=,!=,>,<)
            /// </summary>
            private string GetSQL_NUMBER(WordFieldValue Value, string Field, SentenceWordOperatorTypes Operation)
            {
                Hashtable Templ = new Hashtable();
                Hashtable Params = new Hashtable();
                
                PopulateTemplate(Templ,Params,"decimal(15,2)",Value.Value.ToString (), Field);
                

                //Шабон- параметры- результат
                string sTemplate = Templ[Operation].ToString(); ;
                string[] arrParams = (string[])Params[Operation];
                return string.Format(sTemplate, arrParams); 
                
            }

            /// <summary>
            /// Формирует sql выражение для поля TXT операциям (=,!=,>,<)
            /// </summary>
            private string GetSQL_Date(string Value, string Field, SentenceWordOperatorTypes Operation)
            {
                Hashtable Templ = new Hashtable();
                Hashtable Params = new Hashtable();

                PopulateTemplate(Templ, Params, "datetime", Value, Field);


                //Шабон- параметры- результат
                string sTemplate = Templ[Operation].ToString(); ;
                sTemplate = sTemplate.Replace("CompareValueText_Between", "CompareValueText_Between_DT");
                string[] arrParams = (string[])Params[Operation];
                return string.Format(sTemplate, arrParams);

            }

            /// <summary>
            /// Формирует sql выражение для поля Bool операциям (=,!=,>,<)
            /// </summary>
            private string GetSQl_Bool(bool Value, string Field, SentenceWordOperatorTypes Operation)
            {
                 switch (Operation)
                {
                    case SentenceWordOperatorTypes.EQUALLY  :
                        return " PropertyName = '" + Field + "' AND  PropertyValue = '" + Value.ToString() + "'"; 
                 }

                 return string.Empty;
            }


            static void PopulateTemplate(Hashtable Templ, Hashtable Params, string ParamType, string Value, string Field)
            {


                Templ.Add(SentenceWordOperatorTypes.EQUALLY, " PropertyName = '{0}' AND  PropertyValue = {1}");
                Params.Add(SentenceWordOperatorTypes.EQUALLY, new string[] { Field, Value });


                Templ.Add(SentenceWordOperatorTypes.NOT_EQUALLY, " PropertyName = '{0}' AND  NOT (PropertyValue = {1}) ");
                Params.Add(SentenceWordOperatorTypes.NOT_EQUALLY, new string[] { Field, Value });


                Templ.Add(SentenceWordOperatorTypes.MORE, " PropertyName = '{0}' AND Cast(PropertyValue as {2}) > " + "Cast({1} as {2})");
                Params.Add(SentenceWordOperatorTypes.MORE, new string[] { Field, Value, ParamType });


                Templ.Add(SentenceWordOperatorTypes.LESS, " PropertyName = '{0}' AND Cast(PropertyValue as {2}) < " + "Cast({1} as {2})");
                Params.Add(SentenceWordOperatorTypes.LESS, new string[] { Field, Value, ParamType });

                Templ.Add(SentenceWordOperatorTypes.BETWEEN, " PropertyName = '" + Field + "' AND  1=dbo.CompareValueText_Between (PropertyValue,'{0}','{1}') ");
                Params.Add(SentenceWordOperatorTypes.BETWEEN, Value.Split(";".ToCharArray()[0]));

            }

        }

        #region FieldValue
        /// <summary>
        /// Значение поля
        /// </summary>
        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue : EntitySingle
        {
            public override string ID // перегружен только чтобы удалить SrchItemInfo
            {
                get
                {
                    return base.ID;
                }
                set
                {
                    base.ID = value;
                }
            }
            public string Value { get; set; }
            public override string ToString()
            {
                return Value;
            }
        }
        
        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_Number : WordFieldValue
        {
            new  public decimal Value { get; set; }
            public override string ToString()
            {
                return Value.ToString ();
            }
        }

        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_NumberBetween : WordFieldValue
        {
            public decimal StartValue { get; set; }
            public decimal EndValue { get; set; }
 
        }

        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_StringBetween : WordFieldValue 
        {
            public string StartValue { get; set; }
            public string EndValue { get; set; }
            public override string ToString()
            {
                return StartValue + ";" + EndValue;
            }
        }
       
        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_Date : WordFieldValue
        {
            public DateTime StartValue { get; set; }        

            public override string ToString()
            {
                return StartValue.ToString();
            }
        }

        [IsNotSrchStorageLinksObject]
        public class WordFieldValue_Bool : WordFieldValue
        {
            public bool  bValue { get; set; }

            public override string ToString()
            {
                return bValue.ToString();
            }
        }
        
        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_DateBetween: WordFieldValue
        {
            public DateTime  StartValue { get; set; }
            public DateTime EndValue { get; set; }

            public override string ToString()
            {
                return StartValue.ToString() + ";" + EndValue.ToString();
            }
        }

        [IsNotSrchStorageLinksObject]  
        public class WordFieldValue_TreeValue : WordFieldValue
        {
            new public  TreeValueProperty Value { get; set; }
        }

        #endregion
        
        [IsNotSrchStorageLinksObject]  
        public class SentenceWordField :EntitySingle 
        {
            public override string ID // перегружен только чтобы удалить SrchItemInfo
            {
                get
                {
                    return base.ID;
                }
                set
                {
                    base.ID = value;
                }
            }
            
            /// <summary>
            /// Пример(Контракт.Менеджер.ФИО)
            /// </summary>
            public string TxtKey { get; set; }
            public SrchItemInfo.PropertyControlType PropertyType{ get; set; }
            public SentenceWordOperatorTypes OperationType { get; set; }
            public WordFieldValue Value { get; set; }
            
            /// <summary>
            /// Уроавень параметра в дереве
            /// </summary>
            public int Level 
            { 
                get {return ID.Split(".".ToCharArray()[0]).Length;}
            }

            public string GetSql() 
            {
                WordFieldScriptor w = new WordFieldScriptor();
                return w.GetSql(this);
                
            }            

        }


        public override string ID
        {
            get
            {
                return base.ID;
            }
            set
            {
                base.ID = value;
            }
        }
        
        public static string  ConvertWordOperatorTypesToText(SentenceWordOperatorTypes Value)
        {
            switch (Value)
            { 
                case SentenceWordOperatorTypes .BETWEEN :
                    return "между";
                
                case SentenceWordOperatorTypes.EQUALLY :
                    return "равно";

                case SentenceWordOperatorTypes.LESS :
                    return "меньше";

                case SentenceWordOperatorTypes.MORE:
                    return "больше";

                case SentenceWordOperatorTypes.NOT_EQUALLY:
                    return "не равно";

                case SentenceWordOperatorTypes.LIKE:
                    return "включает";
            }
            return string.Empty;

        }

        public static string ConvertWhereSentenceWordTypesToTxt(WhereSentenceWordTypes Value)
        {
            if (Value == WhereSentenceWordTypes.AND)
                return "и";
            return "или";

        }
        /// <summary>
        /// Тип слова OR/AND
        /// </summary>
        public WhereSentenceWordTypes WordType { get; set; }

        //List<SentenceWordField> _WordFields = new List<SentenceWordField>();
        /// <summary>
        /// Поля слова
        /// </summary>
        //public List<SentenceWordField> WordFields { get { return _WordFields; } }

        EntityList _WordFields = new EntityList();
        public EntityList WordFields { get { return _WordFields; } }

        [EntityNonSerialized]
        public bool ImTmp { get; set; }

        /// <summary>
        /// Вернуть первое поле слова
        /// </summary>
        /// <returns></returns>
        public WhereSentenceWord.SentenceWordField  GetFirstField()
        {
            WhereSentenceWord.SentenceWordField[] f = WordFields.ToArray<WhereSentenceWord.SentenceWordField>();
             if(f.Length ==0)
                    return null ;
            return f[0];
        }
        /// <summary>
        /// Возвращает поля всей ветки 
        /// </summary>
        /// <returns></returns>
        public List<SentenceWordField> GetAllWordFields()
        {   
            List<SentenceWordField> res = new List<SentenceWordField>();
            GetAllWordFields_Loop(this, res);
            return res;

        }

        
        public List<SentenceWordField> GetAllWordFields(SrchItemInfo.PropertyControlType FieldsType)
        {
            List<SentenceWordField> res = new List<SentenceWordField>();
            GetAllWordFields_Loop(this, res);

            List<SentenceWordField> result = new List<SentenceWordField>();
            foreach (SentenceWordField i in res)
            {
                if (i.PropertyType.Equals(FieldsType))
                    result.Add(i);
            }

            return result;

        }

        public  List<SentenceWordField> GetAllWordFields(WhereSentenceWord.WhereSentenceWordTypes Type )
        {
            List<SentenceWordField> res = new List<SentenceWordField>();
            GetTypeAllWordFields_Loop(this, res, Type);
            return res;
        }

        public List<SentenceWordField> GetAllWordFields(WhereSentenceWord.WhereSentenceWordTypes Type, SrchItemInfo.PropertyControlType    ExcludeFieldsType)
        {
            List<SentenceWordField> res = new List<SentenceWordField>();
            GetTypeAllWordFields_Loop(this, res, Type);
             
            List<SentenceWordField> result = new List<SentenceWordField>();
            foreach (SentenceWordField i in res)
            {
                if (!i.PropertyType.Equals(ExcludeFieldsType))
                    result.Add(i);
                }

            return result;
        }

        private void GetTypeAllWordFields_Loop(WhereSentenceWord NextSentence, List<SentenceWordField> res, WhereSentenceWord.WhereSentenceWordTypes Type)
        {
            foreach (SentenceWordField item in NextSentence.WordFields)
            {
                if(NextSentence.WordType ==Type)
                                    res.Add(item);
            }
            foreach (WhereSentenceWord i in NextSentence.ChildNodes)
            {
                GetTypeAllWordFields_Loop(i, res, Type);
            }
        }

        private void GetAllWordFields_Loop(WhereSentenceWord NextSentence, List<SentenceWordField> res)
        {
            NextSentence.WordFields.Reset();
            foreach (SentenceWordField item in NextSentence.WordFields)
            {
                res.Add(item);
            }

            NextSentence.ChildNodes.Reset();
            foreach (WhereSentenceWord i in NextSentence.ChildNodes)
            {
                GetAllWordFields_Loop(i, res); 
            }
        }



        /// <summary>
        /// Пройтись по дереву вниз - сформировать предложение
        /// </summary>
        /// <returns></returns>
        public  string GetSql() 
        {
            StringBuilder sb = new StringBuilder();
            GetSqlNode(sb, this);            
            return sb.ToString(); 
        }
        private void GetSqlNode(StringBuilder sb, WhereSentenceWord NextWord)
        {
            bool bCh = NextWord.WordFields.Count > 0;
            sb.Append(bCh?"(":"");
            foreach (SentenceWordField item in NextWord.WordFields)
            {
                sb.Append(item.GetSql());
            }
            sb.Append(bCh ? ")" : "");

            foreach (WhereSentenceWord item in NextWord.ChildNodes)
            {
                sb.Append(item.WordType.ToString() + " (");
                GetSqlNode(sb, item);
                sb.Append(")");
            }
        }


        /// <summary>
        /// Вернуть операции разрешенные для данного типа
        /// </summary>
        /// <param name="?"></param>
        public static SentenceWordOperatorTypes[] GetAllowedOperatorsForPropertyType(SrchItemInfo.PropertyControlType SentenceWordOperatorTypesVal )
        {
            return WordFieldScriptor.AllowedOperators(SentenceWordOperatorTypesVal);

        }
    }
}
